﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

using System.Configuration;
using System.Data.Common;
using MySql.Data.Common;
using MySql.Data.MySqlClient;

namespace CRMFramework
{
    public static class DBhelper
    {

        //props connect string
        public static string DBConnectionString { get; set; }

        static DBhelper()
        {
            DBConnectionString = ConfigurationManager.ConnectionStrings["CrmConn"].ToString();
        }

        #region  ExecuteNonQuery   ExecuteNonQuerySP

        //Excute database command string
        public static void ExecuteNonQuery(string commandString)
        {
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(commandString, cn as MySqlConnection);
            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        //Excute database StoreProcedure ,no output 
        public static void ExecuteNonQuerySP(string SPName)
        {
            DbConnection cn = new MySqlConnection(DBConnectionString);
            MySqlCommand cmd = new MySqlCommand(SPName, cn as MySqlConnection);

            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        //Over load include Param
        public static void ExecuteNonQuerySP(string SPName, DbParameter[] commandParameters)
        {
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(SPName, cn as MySqlConnection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            foreach (DbParameter parm in commandParameters)
            {
                cmd.Parameters.Add(parm);
            }

            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        #endregion

        #region ExecuteDatasetQuery   ExecuteDatasetSP

        //Excute database command string returen dataset
        public static DataSet ExecuteDatasetQuery(string spName)
        {
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(spName, cn as MySqlConnection);
            DbDataAdapter da = new MySqlDataAdapter();
            DataSet ds = new DataSet();

            cn.Open();
            da.SelectCommand = cmd;
                      
            da.Fill(ds);
            cn.Close();
            return ds;
        }

        //Excute sql store_Procedure without  param and return dataset
        public static DataSet ExecuteDatasetSP(string spName)
        {
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(spName, cn as MySqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            DbDataAdapter da = new MySqlDataAdapter();
            DataSet ds = new DataSet();

            cn.Open();
            da.SelectCommand = cmd;
            da.Fill(ds);
            cn.Close();
            return ds;
        }

        //over load Excute sql store_Procedure with param and return dataset
        public static DataSet ExecuteDatasetSP(string spName, DbParameter[] commandParameters)
        {
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(spName, cn as MySqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;

            foreach (DbParameter parm in commandParameters)
            {
                cmd.Parameters.Add(parm);
            }

            DbDataAdapter da = new MySqlDataAdapter();
            DataSet ds = new DataSet();

            cn.Open();
            da.SelectCommand = cmd;
            da.Fill(ds);
            cn.Close();
            return ds;
        }

        #endregion

        #region GetParameter

        //Set param and it's value to dbparameter
        public static MySqlParameter SetParameter(string name, object value)
        {
            MySqlParameter dataParameter = new MySqlParameter();
            dataParameter.ParameterName = name;
            dataParameter.Value = value;
            return dataParameter;
        }

        //Set parameter with name,datatype and direction for input
        public static MySqlParameter SetParameter(string name, DbType dbType, ParameterDirection direction)
        {

            MySqlParameter dataParameter = new MySqlParameter();
            dataParameter.DbType = dbType;
            dataParameter.Direction = direction;
            dataParameter.ParameterName = name;

            if (dataParameter is MySqlParameter)
            {
                MySqlParameter dbDataParameter = (MySqlParameter)dataParameter;

            }
            return dataParameter;
        }

        //Set param for output 
        public static MySqlParameter SetParameter(string name, ParameterDirection direction)
        {

            MySqlParameter dataParameter = new MySqlParameter();
            dataParameter.Direction = direction;
            dataParameter.ParameterName = name;

            if (dataParameter is DbParameter)
            {
                MySqlParameter dbDataParameter = (MySqlParameter)dataParameter;
            }
            return dataParameter;
        }

        #endregion
    }
}
